package com.youlian.cloud.dao;

import com.youlian.cloud.core.exception.CoreException;
import com.youlian.cloud.dao.model.JdbcParamsModel;
import com.youlian.cloud.core.bean.PageModel;
import com.youlian.cloud.dao.params.BeanSqlParameterSource;
import com.youlian.cloud.dao.params.MapDataSqlParameterSource;
import com.youlian.cloud.dao.params.NamedParameterUtils2;
import com.youlian.cloud.dao.params.ParsedSql2;
import com.youlian.cloud.dao.row.ThirdRowMapper;
import com.youlian.cloud.spring.method.CheckSqlProcess;
import com.youlian.cloud.spring.method.DefaultCheckSQLProcess;
import com.youlian.cloud.utils.BeanPropertyUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import javax.persistence.Column;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 增强的持久层操作
 *
 * @author Liukx
 * @create 2018-04-20 15:36
 * @email liukx@elab-plus.com
 **/
public class BasicBaseDao {

    private Logger logger = LoggerFactory.getLogger(BasicBaseDao.class);

    private JdbcTemplate jdbcTemplate;

    private CheckSqlProcess checkSqlProcess = new DefaultCheckSQLProcess();

    public void setCheckSqlProcess(CheckSqlProcess checkSqlProcess) {
        this.checkSqlProcess = checkSqlProcess;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        if (namedParameterJdbcTemplate == null) {
            namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        }
        return namedParameterJdbcTemplate;
    }

    /**
     * 缓存大小
     */
    private volatile int cacheLimit = NamedParameterJdbcTemplate.DEFAULT_CACHE_LIMIT;

    public void setCacheLimit(int cacheLimit) {
        this.cacheLimit = cacheLimit;
    }

    public int getCacheLimit() {
        return this.cacheLimit;
    }

    @SuppressWarnings("serial")
    private final Map<String, ParsedSql2> parsedSqlCache =
            new LinkedHashMap<String, ParsedSql2>(cacheLimit, 0.75f, true) {
                @Override
                protected boolean removeEldestEntry(Map.Entry<String, ParsedSql2> eldest) {
                    return size() > getCacheLimit();
                }
            };

    /**
     * 参数转换 , 参数类型分别为 Map,Model
     *
     * @param params
     * @return
     */
    private SqlParameterSource getSqlParameterSource(Object params) {
        SqlParameterSource sqlParameterSource = null;
        if (params instanceof Map) {
            sqlParameterSource = new MapSqlParameterSource((Map) params);
        } else {
            sqlParameterSource = new BeanPropertySqlParameterSource(params);
        }
        return sqlParameterSource;
    }

    /**
     * 将解析后的sql对象存放到缓存当中
     * todo 不过开始觉得这样的缓存也没有多大的必要,因为每次都要将sql和参数进行绑定一遍,得到一样的概率太低了,命中率不高
     *
     * @param sql
     * @return
     */
    protected ParsedSql2 getParsedSql2(String sql) {
        if (getCacheLimit() <= 0) {
            return NamedParameterUtils2.parseSqlStatement(sql);
        }
        synchronized (this.parsedSqlCache) {
            ParsedSql2 parsedSql = this.parsedSqlCache.get(sql);
            if (parsedSql == null) {
                parsedSql = NamedParameterUtils2.parseSqlStatement(sql);
                this.parsedSqlCache.put(sql, parsedSql);
            }
            return parsedSql;
        }
    }


    /**
     * 通用sql处理方式
     *
     * @param sql   sql语句
     * @param param 参数
     * @return
     */
    private JdbcParamsModel commonParseSql(String sql, Object param) throws Exception {
        long start = System.currentTimeMillis();
        JdbcParamsModel model = new JdbcParamsModel();
        if (param instanceof String) {
            model.setSql(sql);
            model.setObjects(ArrayUtils.toArray(param));
            logger.debug(" 转换后的sql - " + sql);
            logger.debug(" 对应参数    -  " + param);
            return model;
        }
        // 将sql和参数进行绑定
        ParsedSql2 parsedSql = getParsedSql2(sql);
        // 解析参数类型 , 这里会根据Map和Object对象做装换
        SqlParameterSource sqlParameterSource = getSqlParameterSource(param);
        // 获取有效的参数,将为null和""的参数给替换掉
        Object[] data = NamedParameterUtils2.buildValueArray(parsedSql, sqlParameterSource, null);
        // 正常解析sql为jdbc与参数对应
        String s = NamedParameterUtils2.substituteNamedParameters(parsedSql, sqlParameterSource);
        // 校验第一个参数为空的情况下
        s = NamedParameterUtils2.validFirstCheckSql(sql, s, parsedSql, sqlParameterSource);
        // 替换动态表名存在的情况下
        s = NamedParameterUtils2.replaceDynamic(s, sqlParameterSource);

        logger.debug(" 转换后的sql - " + s);
        logger.debug(" 对应参数    -  " + Arrays.toString(data));

        model.setSql(s);
        model.setObjects(data);

        // 校验SQL是否符合规范
        checkSqlProcess.checkProcess(model, param);
        long time = System.currentTimeMillis() - start;
        logger.debug(" sql 语句处理耗时 : " + time);
        return model;
    }

    /**
     * 查询列表对象
     *
     * @param sql         sql语句
     * @param o           参数对象
     * @param elementType 返回结果对象
     * @param <T>
     * @return
     */
    public <T> List<T> executeQueryList(String sql, Object o, Class<T> elementType) throws Exception {
        JdbcParamsModel jdbcParamsModel = commonParseSql(sql, o);
        long start = System.currentTimeMillis();
        List<T> ts = (List<T>) this.jdbcTemplate.query(jdbcParamsModel.getSql(), jdbcParamsModel.getObjects(), new TimeRowMapperResultSetExtractor(new ThirdRowMapper<T>(elementType)));
        long time = System.currentTimeMillis() - start;
        logger.debug(" SQL 执行耗时 : " + time);
        return ts;
    }

    /**
     * 查询一个分页对象
     *
     * @param pageModel   实体对象
     * @param sql         具体的SQL语句
     * @param o           参数值
     * @param elementType 返回对象
     * @param <T>
     * @return
     */
    public <T> List<T> executeQueryPage(PageModel pageModel, String sql, Object o, Class<T> elementType) throws Exception {
        JdbcParamsModel jdbcParamsModel = commonParseSql(sql, o);
        long start = System.currentTimeMillis();
        List<T> ts = this.jdbcTemplate.query(jdbcParamsModel.getSql(), jdbcParamsModel.getObjects(), new ThirdRowMapper<T>(elementType));
        long time = System.currentTimeMillis() - start;
        logger.debug(" SQL 执行耗时 : " + time);
        return ts;
    }

    /**
     * 获取一个实体对象,这里已经是底层的了
     *
     * @param sql         SQL语句
     * @param obj         参数对象
     * @param elementType 返回对象封装
     * @param <T>
     * @return
     */
    public <T> List<T> queryForList(String sql, Object[] obj, Class<T> elementType) {
        List<T> ts = this.jdbcTemplate.query(sql, obj, new ThirdRowMapper<T>(elementType));
        return ts;
    }

    /**
     * 执行一个查询语句返回一个总数集合
     *
     * @param sql
     * @param arg
     * @return
     */
    public int executeQueryCount(String sql, Object arg) throws Exception {
        JdbcParamsModel jdbcParamsModel = commonParseSql(sql, arg);
        long start = System.currentTimeMillis();
        Integer count = this.jdbcTemplate.queryForObject(jdbcParamsModel.getSql(), jdbcParamsModel.getObjects(), Integer.class);
        long time = System.currentTimeMillis() - start;
        logger.debug(" SQL 执行耗时 : " + time);
        logger.debug(" 获取到的总数 : " + count);
        return count;
    }

    /**
     * 查询一个Map对象的集合
     *
     * @param sql SQL语句
     * @param o   参数
     * @return
     */
    public List<Map<String, Object>> executeQueryForListMap(String sql, Object o) throws Exception {
        JdbcParamsModel jdbcParamsModel = commonParseSql(sql, o);
        long start = System.currentTimeMillis();
        List<Map<String, Object>> maps = this.jdbcTemplate.queryForList(jdbcParamsModel.getSql(), jdbcParamsModel.getObjects());
        long time = System.currentTimeMillis() - start;
        logger.debug(" SQL 执行耗时 : " + time);
        return maps;
    }

    /**
     * 查询大量数据返回的情况下
     *
     * @param sql         SQL语句
     * @param o           参数值
     * @param elementType 返回对象
     * @param <T>
     * @return
     */
    public <T> List<T> executeQueryBigDataList(String sql, Object o, Class<T> elementType) throws Exception {
        JdbcParamsModel jdbcParamsModel = commonParseSql(sql, o);
        List<T> ts = queryBigDataForList(jdbcParamsModel.getSql(), jdbcParamsModel.getObjects(), elementType);
        return ts;
    }

    /**
     * 查询大数据量的情况下,返回对象集合列表
     *
     * @param sql         sql语句
     * @param args        参数  [Map or Model]
     * @param elementType 返回出参结果类型
     * @param <T>
     * @return
     * @throws DataAccessException
     */
    public <T> List<T> queryBigDataForList(String sql, Object[] args, Class<T> elementType) throws DataAccessException {
        long start = System.currentTimeMillis();
        List<T> query = (List<T>) this.jdbcTemplate.query(sql, args, new TimeRowMapperResultSetExtractor(new ThirdRowMapper<T>(elementType)));
        long time = System.currentTimeMillis() - start;
        logger.debug(" SQL 执行耗时 : " + time);
        return query;
    }

    /**
     * 查询单个对象
     *
     * @param sql         SQL语句
     * @param o           默认可以传 Map、Model
     * @param elementType 出参类型指定
     * @param <T>
     * @return
     */
    public <T> T executeQueryObject(String sql, Object o, Class<T> elementType) throws Exception {
        JdbcParamsModel jdbcParamsModel = commonParseSql(sql, o);
        long start = System.currentTimeMillis();
        RowMapper<T> rm = ThirdRowMapper.newInstance(elementType);
        T ts = this.jdbcTemplate.queryForObject(jdbcParamsModel.getSql(), jdbcParamsModel.getObjects(), rm);
        long time = System.currentTimeMillis() - start;
        logger.debug(" SQL 执行耗时 : " + time);
        return ts;
    }


    /**
     * 查询一个数据集合对象,根据自定义的数据转换器返回
     *
     * @param sql SQL语句
     * @param o   参数对象
     * @param rm  返回对象接口
     * @param <T>
     * @return
     */
    public <T> List<T> executeQueryMapper(String sql, Object o, RowMapper<T> rm) throws Exception {
        JdbcParamsModel jdbcParamsModel = commonParseSql(sql, o);
        long start = System.currentTimeMillis();
        List<T> resultList = this.jdbcTemplate.query(jdbcParamsModel.getSql(), jdbcParamsModel.getObjects(), rm);
        long time = System.currentTimeMillis() - start;
        logger.debug(" SQL 执行耗时 : " + time);
        return resultList;
    }

    /**
     * 查询返回一个实体对象
     *
     * @param sql sql语句
     * @param o   对象信息
     * @param rm  映射接口
     * @param <T> 返回对象
     * @return
     */
    public <T> T executeQueryObject(String sql, Object o, RowMapper<T> rm) {
        T result = null;
        try {
            JdbcParamsModel jdbcParamsModel = commonParseSql(sql, o);
            long start = System.currentTimeMillis();
            result = this.jdbcTemplate.queryForObject(jdbcParamsModel.getSql(), jdbcParamsModel.getObjects(), rm);
            long time = System.currentTimeMillis() - start;
            logger.debug(" SQL 执行耗时 : " + time);
        } catch (EmptyResultDataAccessException e) {
            return null;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public <T> T executeQuery(String sql, Object[] o, RowMapper<T> rm) {
        T result = this.jdbcTemplate.queryForObject(sql, o, rm);
        return result;
    }

    /**
     * 查询返回一个Map对象
     *
     * @param sql SQL语句
     * @param o   参数对象
     * @return
     */
    public Map<String, Object> executeQueryMap(String sql, Object o) throws Exception {
        JdbcParamsModel jdbcParamsModel = commonParseSql(sql, o);
        long start = System.currentTimeMillis();
        Map<String, Object> map = this.jdbcTemplate.queryForMap(jdbcParamsModel.getSql(), jdbcParamsModel.getObjects());
        long time = System.currentTimeMillis() - start;
        logger.debug(" SQL 执行耗时 : " + time);
        return map;
    }


    /**
     * 添加数据操作
     *
     * @param sql SQL语句
     * @param o   参数对象
     * @return
     */
    public int insert(String sql, Object o) {
        SqlParameterSource sqlParameterSource = getSqlParameterSource(o);
        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        long start = System.currentTimeMillis();
        logger.debug("sql : " + sql);
        this.getNamedParameterJdbcTemplate().update(sql, sqlParameterSource, keyHolder);
        int i = keyHolder.getKey().intValue();
        logger.debug(" params : " + i);
        long time = System.currentTimeMillis() - start;
        logger.debug(" SQL 执行耗时 : " + time);
        return i;
    }


    /**
     * 将内部参数转换成对应的属性器
     *
     * @param bean
     * @param nameMap
     * @return
     */
    private SqlParameterSource getConvertSqlParameterSource(Object bean, Map<String, String> nameMap) {
        SqlParameterSource sqlParameterSource = null;
        if (bean instanceof Map) {
            sqlParameterSource = new MapDataSqlParameterSource((Map<String, ?>) bean);
        } else {
            sqlParameterSource = new BeanSqlParameterSource(bean);
            ((BeanSqlParameterSource) sqlParameterSource).setPropertyMap(nameMap);
        }
        return sqlParameterSource;
    }

    /**
     * 批量操作
     *
     * @param sql  sql语句
     * @param list 数据集合
     * @return
     */
    public int[] batchOperation(String sql, List list) throws Exception {
        if (list != null && list.size() > 0) {
            long start = System.currentTimeMillis();
            logger.debug("sql : " + sql);
            Object o = list.get(0);
            SqlParameterSource[] parameterSource = null;
            Map<String, String> nameMap = null;
            if (o instanceof Map) {
                parameterSource = new MapSqlParameterSource[list.size()];
            } else {
                parameterSource = new BeanSqlParameterSource[list.size()];
                nameMap = BeanPropertyUtils.getColumnProperty(o, Column.class, "name");
            }

            for (int i = 0; i < list.size(); i++) {
                Object bean = list.get(i);
                SqlParameterSource sqlParameterSource = getConvertSqlParameterSource(bean, nameMap);
                parameterSource[i] = sqlParameterSource;
            }
            int[] results = this.getNamedParameterJdbcTemplate().batchUpdate(sql, parameterSource);
            logger.debug(" params size : " + list.size());
            long time = System.currentTimeMillis() - start;
            logger.debug(" SQL 执行耗时 : " + time);
            return results;
        } else {
            throw new CoreException("批量操作数据不能为空 ...");
        }
    }

    /**
     * 修改数据操作
     *
     * @param sql SQL语句
     * @param o   对象
     * @return
     */
    public int update(String sql, Object o) throws Exception {
        JdbcParamsModel jdbcParamsModel = commonParseSql(sql, o);
        long start = System.currentTimeMillis();
        int update = this.jdbcTemplate.update(jdbcParamsModel.getSql(), jdbcParamsModel.getObjects());
        long time = System.currentTimeMillis() - start;
        logger.debug(" SQL 执行耗时 : " + time);
        return update;
    }


}
